Release 10.1A: OpenEdge Development:
Progress 4GL Reference

RUN STORED-PROCEDURE statement

Runs a non-Progress stored procedure or allows you to send SQL to an SQL-based data source using an OpenEdge DataServer.

Syntax 

RUN STORED-PROCEDURE procedure
  [ integer-field = PROC-HANDLE ]
  [ NO-ERROR ]
  [ ( parameter [ , parameter ] ... ) ]

procedure

The name of the stored procedure that you want to run or the Progress built-in procedure name, send-sql-statement, to send SQL to an SQL-based data source.

integer-field = PROC-HANDLE

Assigns a value to the specified integer field or variable (integer-field) that uniquely identifies the stored procedure returning results from the non-OpenEdge database or that uniquely identifies the SQL cursor used to retrieve results from an SQL-based, ODBC-compliant data source.

NO-ERROR

Specifies that any ERROR conditions that the RUN STORED-PROCEDURE statement produces are suppressed. Before you close a stored procedure, check the ERROR-STATUS handle for information on any errors that occurred. You receive an error when you attempt to close a stored procedure that did not start.

Note: This option must appear before any run-time parameter list.

parameter

A run-time parameter to be passed to the stored procedure. A parameter has the following syntax: 

[ INPUT | OUTPUT | INPUT-OUTPUT ]
  [ PARAM parameter-name = ] expression

An expression is a constant, field name, variable name, or expression. INPUT is the default. OUTPUT and INPUT-OUTPUT parameters must be record fields or program variables. For ORACLE, OUTPUT and INPUT-OUTPUT work the same way.

If you run send-sql-statement for an SQL-based data source, you must pass a single character expression parameter containing the SQL statement you want the data source to execute.

If you do not specify parameter-name (the name of a keyword parameter defined by the stored procedure), you must supply all of the parameters in correct order. If you do specify parameter-name, you must precede your assignment statement with the keyword PARAM. If you do not supply a required parameter, and no default is specified in the stored procedure, you receive a run-time error.

Examples

This procedure runs the ORACLE stored procedure pcust and writes the results of the stored procedure into the Progress-supplied buffer, proc-text-buffer. The same code works for accessing a stored procedure from an ODBC-compliant data source. 

DEFINE VAR intvar AS INTEGER.

RUN STORED-PROCEDURE pcust intvar = PROC-HANDLE 
(10, OUTPUT 0, OUTPUT 0) NO-ERROR.
FOR EACH proc-text-buffer WHERE PROC-HANDLE = intvar:
  DISPLAY proc-text-buffer.
END.
IF ERROR-STATUS:ERROR
THEN DO:
  MESSAGE "Stored Procedure failed to run".
END.
ELSE CLOSE STORED-PROCEDURE pcust WHERE PROC-HANDLE = intvar.

This procedure uses the send-sql-statement option of the RUN STORED-PROCEDURE statement to send SQL to ORACLE. It writes the results of the stored procedure into the Progress-supplied buffer, proc-text-buffer. The same code works for sending SQL to an ODBC-compliant data source: 

DEFINE VAR handle1 AS INTEGER.

RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
     ("SELECT name, cust_num FROM customer").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
   display proc-text.
   END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.
END.

This code example shows how to trap errors from the non-OpenEdge RDBMS within a procedure: 

DEFINE VAR h1 AS INTEGER.
DEFINE VAR j AS INTEGER.
RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE NO-ERROR
  ("select count (*) from xxx.customer where name between ’A’ and ’Z’ ").

IF ERROR STATUS:ERROR THEN DO:
  DO j = 1 TO ERROR-STATUS:NUM-MESSAGES:
    MESSAGE "error" ERROR-STATUS:GET-NUMBER(j)
        ERROR-STATUS:GET-MESSAGE(j).
  END.
END.

CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1.

Notes

See also

CLOSE STORED-PROCEDURE statement, PROC-HANDLE function, PROC-STATUS function

Copyright © 2005 Progress Software Corporation
 www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095